﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace CreateDatabase
{
	class CreateTables
	{

		static public string ConnectionString = ApplicationConfiguration.ConnectionString;

		static public void CreateBlogTable()
		{
			string sql = "USE [BlogKickstart] \r\n GO \r\n  \r\n /****** Object:  Table [dbo].[Blogs]    Script Date: 11/03/2012 13:27:41 ******/ \r\n SET ANSI_NULLS ON \r\n GO \r\n  \r\n SET QUOTED_IDENTIFIER ON \r\n GO \r\n  \r\n CREATE TABLE [dbo].[Blogs]( \r\n 	[BlogID] [int] IDENTITY(1,1) NOT NULL, \r\n 	[Name] [nvarchar](256) NULL, \r\n 	[Url] [nvarchar](max) NULL, \r\n 	[StyleID] [int] NULL, \r\n  CONSTRAINT [PK_Blogs] PRIMARY KEY CLUSTERED  \r\n ( \r\n 	[BlogID] ASC \r\n )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] \r\n ) ON [PRIMARY] \r\n  \r\n GO \r\n  \r\n ALTER TABLE [dbo].[Blogs]  WITH CHECK ADD  CONSTRAINT [FK_Blogs_Blogs] FOREIGN KEY([BlogID]) \r\n REFERENCES [dbo].[Blogs] ([BlogID]) \r\n GO \r\n  \r\n ALTER TABLE [dbo].[Blogs] CHECK CONSTRAINT [FK_Blogs_Blogs] \r\n GO \r\n  \r\n ALTER TABLE [dbo].[Blogs]  WITH CHECK ADD  CONSTRAINT [FK_blogs_styleID] FOREIGN KEY([StyleID]) \r\n REFERENCES [dbo].[Styles] ([StyleID]) \r\n GO \r\n  \r\n ALTER TABLE [dbo].[Blogs] CHECK CONSTRAINT [FK_blogs_styleID] \r\n GO \r\n  \r\n  \r\n ";
			runQuery(sql);
		}

		static public void CreatePostsTable()
		{
			string sql = "USE [BlogKickstart] \r\n GO \r\n  \r\n /****** Object:  Table [dbo].[Posts]    Script Date: 11/03/2012 13:47:47 ******/ \r\n SET ANSI_NULLS ON \r\n GO \r\n  \r\n SET QUOTED_IDENTIFIER ON \r\n GO \r\n  \r\n CREATE TABLE [dbo].[Posts]( \r\n 	[PostID] [int] IDENTITY(1,1) NOT NULL, \r\n 	[BlogID] [int] NOT NULL, \r\n 	[Title] [nvarchar](256) NULL, \r\n 	[UnmarkedContent] [ntext] NULL, \r\n 	[MarkedContent] [ntext] NULL, \r\n 	[Date] [datetime] NULL, \r\n 	[Active] [bit] NULL, \r\n  CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED  \r\n ( \r\n 	[PostID] ASC \r\n )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] \r\n ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] \r\n  \r\n GO \r\n  \r\n ALTER TABLE [dbo].[Posts]  WITH CHECK ADD  CONSTRAINT [FK_Posts_Blogs] FOREIGN KEY([BlogID]) \r\n REFERENCES [dbo].[Blogs] ([BlogID]) \r\n GO \r\n  \r\n ALTER TABLE [dbo].[Posts] CHECK CONSTRAINT [FK_Posts_Blogs] \r\n GO \r\n  \r\n  \r\n ";
			runQuery(sql);
		}

		static public void CreateStylesTable()
		{
			string sql = "USE [BlogKickstart] \r\n GO \r\n  \r\n /****** Object:  Table [dbo].[Styles]    Script Date: 11/03/2012 13:49:07 ******/ \r\n SET ANSI_NULLS ON \r\n GO \r\n  \r\n SET QUOTED_IDENTIFIER ON \r\n GO \r\n  \r\n CREATE TABLE [dbo].[Styles]( \r\n 	[StyleID] [int] IDENTITY(1,1) NOT NULL, \r\n 	[StyleNM] [nvarchar](100) NOT NULL, \r\n PRIMARY KEY CLUSTERED  \r\n ( \r\n 	[StyleID] ASC \r\n )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] \r\n ) ON [PRIMARY] \r\n  \r\n GO \r\n  \r\n  \r\n ";
			runQuery(sql);
		}

		static public void CreateUsersTable()
		{
			string sql = "USE [BlogKickstart] \r\n GO \r\n  \r\n /****** Object:  Table [dbo].[Users]    Script Date: 11/03/2012 13:50:20 ******/ \r\n SET ANSI_NULLS ON \r\n GO \r\n  \r\n SET QUOTED_IDENTIFIER ON \r\n GO \r\n  \r\n CREATE TABLE [dbo].[Users]( \r\n 	[UserID] [int] IDENTITY(1,1) NOT NULL, \r\n 	[FirstNM] [nvarchar](100) NOT NULL, \r\n 	[LastNM] [nvarchar](100) NOT NULL, \r\n 	[Birthday] [datetime2](7) NULL, \r\n 	[CreateDate] [datetime2](7) NULL, \r\n 	[BlogID] [int] NOT NULL, \r\n 	[Pass] [nvarchar](100) NULL, \r\n 	[UserName] [nvarchar](100) NOT NULL, \r\n PRIMARY KEY CLUSTERED  \r\n ( \r\n 	[UserID] ASC \r\n )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] \r\n ) ON [PRIMARY] \r\n  \r\n GO \r\n  \r\n ALTER TABLE [dbo].[Users]  WITH CHECK ADD FOREIGN KEY([BlogID]) \r\n REFERENCES [dbo].[Blogs] ([BlogID]) \r\n GO \r\n  \r\n  \r\n ";
			runQuery(sql);
		}

		static public void runQuery(string command)
		{
			SqlConnection sqlConnection = new SqlConnection(ConnectionString);
			Server server = new Server(new ServerConnection(sqlConnection));
			server.ConnectionContext.ExecuteNonQuery(command);
		}
	}
}
